﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using SoufunData.DB;

namespace SoufunData
{
    public partial class ExToDbLandTransaction : DevExpress.XtraEditors.XtraForm
    {
        public ExToDbLandTransaction()
        {
            InitializeComponent();
        }

        private void btn_excelPath_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {

            openFileDialog1.Filter = "Excel文件|*.xls;*.xlsx";
            openFileDialog1.FileName = "";
            openFileDialog1.ShowDialog();
            string fileName = openFileDialog1.FileName;
            btn_excelPath.Text = fileName;

            gridControl2.DataSource = null;
            gridView2.Columns.Clear();
            gridView2.GroupPanelText = "请选择Excel文件";
            if (fileName == "")
            {
                return;
            }
            DataSet ds = Common.LoadDataFromExcel(fileName);
            if (ds == null || ds.Tables.Count == 0)
            {
                string strError = "读取Excel时发生错误，请核对：\r\n ";
                strError += "1，导入时请在Excel中关闭想要导入的文件！\r\n";
                strError += "2，导入的Excel表中的工作表名称必须为Sheet1,否者程序无法读取！\r\n";
                strError += "3，请确保您的计算机安装了Microsoft Office！\r\n";
                strError += "4，请确保导入的Excel文件正常！";
                XtraMessageBox.Show(strError);
                btn_excelPath.Text = "";
                return;
            }            
            if (!CheckExcelColumnsName(ds.Tables[0]))
            {
                btn_excelPath.Text = "";
                return;
            }

            List<string> deleteColumns = new List<string>();
            foreach (DataColumn column in ds.Tables[0].Columns)
            {
                if (column.ColumnName.Contains("F"))
                {
                    deleteColumns.Add(column.ColumnName);
                }
            }
            foreach (string column in deleteColumns)
            {
                ds.Tables[0].Columns.Remove(column);
            }
            if (!CheckExcelColumnsName(ds.Tables[0]))
            {
                btn_excelPath.Text = "";
                return;
            }
            gridControl2.DataSource = ds.Tables[0];
            gridView2.GroupPanelText = "共有" + ds.Tables[0].Rows.Count + "条数据";
        }


        private void AddData()
        {
           
            DataTable dtOld = (DataTable)gridControl2.DataSource;

            DataTable dt = dtOld.Copy();
            dt.Rows.Clear();
            foreach (DataRow dataRow in dtOld.Rows)
            { 
                dt.Rows.Add(dataRow.ItemArray);
            }

            SqlClass sqlClass = new SqlClass();
            List<string> existProject = new List<string>();
            foreach (DataRow row in dt.Rows)
            {
                string parcelId = row["地块编号"].ToString();
                if (sqlClass.LandTransationExistId(parcelId))
                {
                    existProject.Add(parcelId);
                }
            }
            if (existProject.Count > 0)
            {
                StringBuilder strWarming = new StringBuilder();
                strWarming.Append("您选择的Excel表格中的以下地块在数据库中已存在,不能再次添加：\r\n");
                foreach (string sproName in existProject)
                {
                    strWarming.Append(sproName);
                    strWarming.Append("\r\n");
                }
                XtraMessageBox.Show(strWarming.ToString());
                BeginInvoke(new MethodInvoker(() =>
                {
                    btn_excelPath.Text = "";
                    gridControl2.DataSource = null;
                    btn_excelPath.Enabled = true;
                    btn_add.Enabled = true;
                }));
                return;
            }


            int insertRowsCount = 500;//一次性插入数据库行数
            int succeedrows = 0;//成功插入数据行数
            StringBuilder strSql = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i==12)
                {
                    Console.WriteLine("222");
                }
                DataRow row = dt.Rows[i]; 
                string strLandParcelID = row["地块编号"].ToString();
                string strVendee = row["土地使用权竞得人"].ToString();
                string strGroup = row["所属集团"].ToString();
                string strPrice = row["成交总价（万元）"].ToString();
                if (strPrice.Trim() == "" ||
              strPrice == "-")
                {
                    strPrice = "-1";
                }
                string strPriceOfASquare = row["成交楼面地价（元/㎡）"].ToString();
                if (strPriceOfASquare.Trim() == "" ||
              strPriceOfASquare == "-")
                {
                    strPriceOfASquare = "-1";
                }
                string strPriceOfAMu = row["成交每亩价（万元/亩）"].ToString();
                if (strPriceOfAMu.Trim() == "" ||
              strPriceOfAMu == "-")
                {
                    strPriceOfAMu = "-1";
                }
                string strPremiumRate = row["溢价率"].ToString();
                if (strPremiumRate.Trim() == "" ||
              strPremiumRate == "-")
                {
                    strPremiumRate = "-1";
                }
                string strPaidTimes = row["竞价次数"].ToString();
                if (strPaidTimes.Trim() == "" ||
              strPaidTimes == "-")
                {
                    strPaidTimes = "-1";
                }
                string strTransactionTime = row["成交时间"].ToString();
                string strNoPriceOfASquare = row["楼面地价排行榜"].ToString();
                if (strNoPriceOfASquare.Trim() == "" ||
           strNoPriceOfASquare == "-")
                {
                    strNoPriceOfASquare = "0";
                }
                string strNoPrice = row["成交总价排行榜"].ToString();
                if (strNoPrice.Trim() == "" || strNoPrice == "-")
                {
                    strNoPrice = "0";
                }
                string strNoGroup = row["企业排行榜"].ToString();
                if (strNoGroup.Trim() == "" || strNoGroup == "-")
                {
                    strNoGroup = "0";
                }
                string strState = row["状态"].ToString();
            

             
                
                if (i % insertRowsCount == 0)
                {
                    strSql = new StringBuilder();
                    strSql.Append("Insert into tlandtransaction (");
                    strSql.Append("LandParcelID,");
                    strSql.Append("Vendee,");
                    strSql.Append("`Group`,");
                    strSql.Append("Price,");
                    strSql.Append("PriceOfASquare,");
                    strSql.Append("PriceOfAMu,");
                    strSql.Append("PremiumRate,");
                    strSql.Append("PaidTimes,");
                    strSql.Append("TransactionTime,");
                    strSql.Append("NoPriceOfASquare,");
                    strSql.Append("NoPrice,");
                    strSql.Append("NoGroup,");
                    strSql.Append("State)");
                     
                    strSql.Append(" value  ");
                }
                strSql.Append("(");
                strSql.Append("'");
                strSql.Append(strLandParcelID);
                strSql.Append("','");
                strSql.Append(strVendee);
                strSql.Append("','");
                strSql.Append(strGroup);
                strSql.Append("',");
                strSql.Append(strPrice);
                strSql.Append(",");
                strSql.Append(strPriceOfASquare);
                strSql.Append(",");
                strSql.Append(strPriceOfAMu);
                strSql.Append(",");
                strSql.Append(strPremiumRate);
                strSql.Append(",");
                strSql.Append(strPaidTimes);
                strSql.Append(",'");
                strSql.Append(strTransactionTime);
                strSql.Append("',");
                strSql.Append(strNoPriceOfASquare);
                strSql.Append(",");
                strSql.Append(strNoPrice);
                strSql.Append(",");
                strSql.Append(strNoGroup);
                strSql.Append(",'");
                strSql.Append(strState);
                strSql.Append("'),");

                if ((i + 1) % insertRowsCount == 0
                    || i == dt.Rows.Count - 1)
                {
                    strSql.Remove(strSql.Length - 1, 1);
                    try
                    {
                        succeedrows += DbHelper.ExecuteSql(strSql.ToString());
                    }
                    catch (Exception ex)
                    {
                        XtraMessageBox.Show(ex.Message);
                    }

                }
            }

            if (dt.Rows.Count == succeedrows)
            {
                XtraMessageBox.Show("添加成功！");
                BeginInvoke(new MethodInvoker(() =>
                {
                    btn_excelPath.Text = "";
                    gridControl2.DataSource = null;
                    gridView2.GroupPanelText = "请选择想要导入的Excel表！";
                }));
            }
            else
            {
                int failedCount = dt.Rows.Count - succeedrows;
                XtraMessageBox.Show("共记" + dt.Rows.Count + "条数据，其中"
                    + failedCount + "条数据添加失败！");
            }
            BeginInvoke(new MethodInvoker(() =>
            {
               
                btn_excelPath.Enabled = true;
                btn_add.Enabled = true;
            }));
        }

        private void btn_add_Click(object sender, EventArgs e)
        {
            if (btn_excelPath.Text.Trim()=="")
            {
                XtraMessageBox.Show("请先选择Excel！");
                return;
            }
            if (!CheckDataValid())
            {
                return;
            }
             
            btn_excelPath.Enabled = false;
            btn_add.Enabled = false;
            Thread t = new Thread(AddData) { IsBackground = true };
            t.Start();
        }

        #region 数据完整性检测
        private bool CheckDataValid()
        {
            DataTable dt = (DataTable)gridControl2.DataSource;
            StringBuilder strErrors = new StringBuilder();
           
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                string strLandParcelID = dr["地块编号"].ToString();
                string strPrice = dr["成交总价（万元）"].ToString();
                string strPriceOfASquare = dr["成交楼面地价（元/㎡）"].ToString();
                string strPriceOfAMu = dr["成交每亩价（万元/亩）"].ToString();
                string strPremiumRate = dr["溢价率"].ToString();
                string strTransactionTime = dr["成交时间"].ToString();
                string strPaidTimes = dr["竞价次数"].ToString();
                string strNoPriceOfASquare = dr["楼面地价排行榜"].ToString();
                string strNoPrice = dr["成交总价排行榜"].ToString();
                string strNoGroup = dr["企业排行榜"].ToString();
                string strState = dr["状态"].ToString();
             
                if (strLandParcelID.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【地块编号】为空！\r\n"); 
                }
                DateTime dateTemp;
                if (!DateTime.TryParse(strTransactionTime, out dateTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【成交时间】不是正确的时间格式！\r\n");
                }
                   
                float floatTemp;
                if (strPrice.Trim() != "" &&
                    strPrice != "-" &&
                    !float.TryParse(strPrice, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【成交总价（万元）】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if (strPriceOfASquare.Trim() != "" &&
                     strPriceOfASquare != "-" &&
                    !float.TryParse(strPriceOfASquare, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【成交楼面地价（元/㎡）】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if (strPriceOfAMu.Trim() != "" &&
                  strPriceOfAMu != "-" &&
                 !float.TryParse(strPriceOfAMu, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【成交每亩价（万元/亩）】的数据格式有误，不是正确的数字格式！\r\n");
                }

                if (strPremiumRate.Trim() != "" &&
                  strPremiumRate != "-" &&
                 !float.TryParse(strPremiumRate, out floatTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【溢价率】的数据格式有误，不是正确的数字格式！\r\n");
                }
                int intTemp;
                if (strPaidTimes.Trim() != "" &&
                  strPaidTimes != "-" &&
                 !int.TryParse(strPaidTimes, out intTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【竞价次数】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if (strNoPriceOfASquare.Trim() != "" &&
           strNoPriceOfASquare != "-" &&
          !int.TryParse(strNoPriceOfASquare, out intTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【楼面地价排行榜】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if (strNoPrice.Trim() != "" &&
           strNoPrice != "-" &&
          !int.TryParse(strNoPrice, out intTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【成交总价排行榜】的数据格式有误，不是正确的数字格式！\r\n");
                }
                if (strNoGroup.Trim() != "" &&
           strNoGroup != "-" &&
          !int.TryParse(strNoGroup, out intTemp))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【企业排行榜】的数据格式有误，不是正确的数字格式！\r\n");
                }


                if (strState.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【状态】为空！\r\n");
                }

            }
            if (strErrors.ToString() != "")
            {                
                XtraMessageBox.Show("您选择的Excel中的数据存在以下错误：\r\n"+
                    strErrors);
                return false;
            }              
            return true;
        }
         
        private bool CheckExcelColumnsName(DataTable dt)
        {
            StringBuilder strErrors = new StringBuilder();
            if (!dt.Columns.Contains("地块编号"))
            {
                strErrors.Append("【地块编号】、");
            }
            if (!dt.Columns.Contains("土地使用权竞得人"))
            {
                strErrors.Append("【土地使用权竞得人】、");
            }
            if (!dt.Columns.Contains("所属集团"))
            {
                strErrors.Append("【所属集团】、");
            }
            if (!dt.Columns.Contains("成交总价（万元）"))
            {
                strErrors.Append("【成交总价（万元）】、");
            }
            if (!dt.Columns.Contains("成交楼面地价（元/㎡）"))
            {
                strErrors.Append("【成交楼面地价（元/㎡）】、");
            }
            if (!dt.Columns.Contains("成交每亩价（万元/亩）"))
            {
                strErrors.Append("【成交每亩价（万元/亩）】、");
            }
            if (!dt.Columns.Contains("溢价率"))
            {
                strErrors.Append("【溢价率】、");
            }
            if (!dt.Columns.Contains("竞价次数"))
            {
                strErrors.Append("【竞价次数】、");
            }
            if (!dt.Columns.Contains("成交时间"))
            {
                strErrors.Append("【成交时间】、");
            }
            if (!dt.Columns.Contains("楼面地价排行榜"))
            {
                strErrors.Append("【楼面地价排行榜】、");
            }
            if (!dt.Columns.Contains("成交总价排行榜"))
            {
                strErrors.Append("【成交总价排行榜】、");
            }
            if (!dt.Columns.Contains("企业排行榜"))
            {
                strErrors.Append("【企业排行榜】、");
            }
            if (!dt.Columns.Contains("状态"))
            {
                strErrors.Append("【状态】、");
            }
           
            if (strErrors.ToString() != "")
            {
                XtraMessageBox.Show("您选择的Excel表数据不正常,缺少以下数据列:\r\n" +
                                    strErrors);
                return false;
            }
            return true;
        }
        #endregion
    }
}